# -*- coding: utf-8 -*-
import gitee_package
import log_package
import ini_package
import pandas_package
from playwright_package import os_package
from gitee_package import re
from playwright_package import time_package
import playwright_package
import excel_package

# 登陆函数
def check_login(zh,mm):
    try:
        log_opt.write_info(f'正在登陆-账号：【{zh}】，密码：【{mm}】...')
        web_opt.page.goto(url)
        web_opt.page.get_by_placeholder("请输入手机号码").click()
        web_opt.page.get_by_placeholder("请输入手机号码").fill(str(zh))
        web_opt.page.get_by_placeholder("请输入登录密码").click()
        web_opt.page.get_by_placeholder("请输入登录密码").fill(str(mm))
        web_opt.page.get_by_role("button", name="登 录").click()
        web_opt.page.wait_for_selector('text=ID:', timeout=5000)
        log_opt.write_success(f'账号：【{zh}】，密码：【{mm}】，登陆成功！')
        return True
    except Exception as e:
        log_opt.write_error(f'账号：【{zh}】，密码：【{mm}】，登陆失败！')
        return False

    finally:
        web_opt.page.wait_for_timeout(2000)

# 下载证书
def download_certificate():
    global sc
    try:
        web_opt.page.get_by_role("link", name="我的证书").click()
        web_opt.page.wait_for_timeout(1000)
        # 下载所有证书
        for c in web_opt.get_child_xpaths('xpath=/html/body/div[1]/section/div/div/div/table/tbody','tr').values():
            # 竞赛
            js = web_opt.page.locator(web_opt.get_child_xpaths(c, 'td')['sub_element_1']).text_content()
            # 赛项
            sx = web_opt.page.locator(web_opt.get_child_xpaths(c,'td')['sub_element_2']).text_content()
            # 组别
            zb = web_opt.page.locator(web_opt.get_child_xpaths(c, 'td')['sub_element_3']).text_content()
            # 奖项
            jx = web_opt.page.locator(web_opt.get_child_xpaths(c, 'td')['sub_element_4']).text_content()
            # 获奖人员
            hjry = web_opt.page.locator(web_opt.get_child_xpaths(c, 'td')['sub_element_5']).text_content()
            # 移除所有换行符和多余空格
            hjry = hjry.replace('\n', '').replace('\t', '')
            # 按逗号分割字符串并去除每个名字前后的空格
            hjry = [name.strip() for name in hjry.split(',') if name.strip()]
            # 下载证书
            xzzs = web_opt.get_child_xpaths(web_opt.get_child_xpaths(c, 'td')['sub_element_6'],'a')['sub_element_1']
            xzzs = web_opt.page.locator(xzzs).get_attribute("href")
            # print(sx)
            # print(zb)
            # print(jx)
            # print(hjry)
            # print(xzzs)

            # print(n)
            photo_folder =f'file/学生证书/{sc}/{js}/{sx}/{zb}/{jx}'
            photo_path =f'{photo_folder}/{"_".join(hjry)}.png'
            # print(photo_path)
            os_opt.make_file_folder(photo_folder)
            log_opt.write_info(f'正在下载【学生证书/{sc}/{js}/{sx}/{zb}/{jx}/{"_".join(hjry)}.png】...')
            if gitee_package.Gitee_Option.download_file_cookies(xzzs, photo_path):
                log_opt.write_success(f'【学生证书/{sc}/{js}/{sx}/{zb}/{jx}/{"_".join(hjry)}.png】下载成功！')
                for h in hjry:
                    ex_info.insert_row_values('下载成功',ex_info.get_sheet_max_rows('下载成功')+1,{
                        '序号':ex_info.get_sheet_max_rows('下载成功'),
                        '学校':sc,
                        '学生姓名':h,
                        '赛项':sx,
                        '组别':zb,
                        '获得奖项':jx,
                        '证书地址':f'学生证书/{sc}/{js}/{sx}/{zb}/{jx}/{"_".join(hjry)}.png'
                    })
                    ex_info.insert_hyperlink('下载成功',ex_info.get_sheet_max_rows('下载成功'),7,f'学生证书/{sc}/{js}/{sx}/{zb}/{jx}/{"_".join(hjry)}.png',f'学生证书/{sc}/{js}/{sx}/{zb}/{jx}/{"_".join(hjry)}.png')
            else:
                log_opt.write_error(f'【学生证书/{sc}/{js}/{sx}/{zb}/{jx}/{"_".join(hjry)}.png】下载失败！')
                for h in hjry:
                    ex_info.insert_row_values('下载失败', ex_info.get_sheet_max_rows('下载失败') + 1, {
                        '序号': ex_info.get_sheet_max_rows('下载失败'),
                        '学校': sc,
                        '学生姓名': h,
                        '赛项': sx,
                        '组别': zb,
                        '备注': '下载出错',
                    })
        return True
    except Exception as e:
        log_opt.write_error(f'下载证书出现错误{e}')
        return False

if __name__ == '__main__':
    # 初始化
    time_opt = time_package.Time_Option()
    time_opt.start_timing()
    log_opt = log_package.Logging_Option()
    log_opt.write_info('正在初始化...')
    os_opt = os_package.Os_Option()
    os_opt.make_file_folder('file')
    os_opt.make_file_folder('log')
    url = 'https://qgqkspt.cngef.org.cn/signin/'
    fz_file_name_path = 'file/金牛区竞赛分组表.xlsx'
    xx_file_name_path = 'file/竞赛信息表.xlsx'
    info_file_name_path = 'file/金牛区学生竞赛证书合集.xlsx'

    # 分学校
    ex_fz = excel_package.Excel_Option(fz_file_name_path)
    ex_fz.open_file()
    ex_xx = excel_package.Excel_Option(xx_file_name_path)
    ex_xx.open_file()
    ex_info = excel_package.Excel_Option(info_file_name_path)
    ex_info.create_new_excel()

    try:
        ex_info.delete_sheet('Sheet')
        ex_info.create_new_sheet('下载成功')
        ex_info.create_new_sheet('下载失败')
        ex_info.create_new_sheet('登陆失败')
        ex_info.write_headers_to_sheet('下载成功',['序号','学校','学生姓名','赛项','组别','获得奖项','证书地址','备注'])
        ex_info.write_headers_to_sheet('下载失败',['序号','学校','学生姓名','赛项','组别','备注'])
        ex_info.write_headers_to_sheet('登陆失败',['序号','学校','学生姓名','账号','密码','备注'])
        ex_info.save_file()
        school_names = ex_fz.get_column_values(ex_fz.list_sheets()[0],2)
        school_names.remove('参赛学校')
        school_names = list(set(school_names))
        while None in school_names:
            school_names.remove(None)
        school_names.sort()
        # print(school_names)
        # 表头
        fz_headers = ex_fz.get_sheet_headers(ex_fz.list_sheets()[0])
        # print(fz_headers)
        xx_headers = ex_xx.get_sheet_headers(ex_xx.list_sheets()[0])
        # print(xx_headers)

        log_opt.write_success('初始化成功！')

        # 筛选
        pd_fz = pandas_package.Big_Data_Option()
        pd_fz.open_file(fz_file_name_path,ex_fz.list_sheets()[0])
        pd_xx = pandas_package.Big_Data_Option()
        pd_xx.open_file(xx_file_name_path,ex_xx.list_sheets()[0])
        pd_fz.replace_nan_with_value('')
        pd_xx.replace_nan_with_value('')
        ex_fz.close_file()
        ex_xx.close_file()
        web_opt = playwright_package.Playwright_Management(headless=True)
        web_opt.page.goto(url)
        for sc in school_names:
            for row_c in pd_fz.view_filtered_data({'参赛学校':sc}):
                # 分辨出学生
                stu_s = []
                stu_s.append(row_c['队员甲'])
                stu_s.append(row_c['队员乙'])
                stu_s.append(row_c['队员丙'])

                while '' in stu_s:
                    stu_s.remove('')
                # print(stu_s)

                # 查询信息
                team_is_ok = False
                for s in stu_s:
                    d = {'1、就读学校：':sc,'2、学生姓名：':s}
                    data = pd_xx.view_filtered_data(d)
                    if not check_login(str(data[0]['8、请输入注册的手机号码：']),str(data[0]['9、请输入注册的密码'])):
                        if not check_login(str(data[0]['8、请输入注册的手机号码：']), str(123456)):
                            ex_info.insert_row_values('登陆失败', ex_info.get_sheet_max_rows('登陆失败') + 1, {
                                '序号': ex_info.get_sheet_max_rows('登陆失败'),
                                '学校': sc,
                                '学生姓名': s,
                                '账号':str(data[0]['8、请输入注册的手机号码：']),
                                '密码':str(data[0]['9、请输入注册的密码']),
                                '备注': '登陆失败',
                            })
                            continue
                    break
                # print('执行查询信息')
                download_certificate()
                web_opt.page.wait_for_timeout(1000)
                # web_opt.page.pause()

    except Exception as e:
        log_opt.write_error(f'程序异常：{e}')
    finally:
        ex_xx.close_file()
        ex_fz.close_file()
        ex_info.save_file()
        log_opt.write_info(f'程序用时：{time_opt.end_timing()}')
        time_opt.timesleep(3)
